package org.tabaddict.widget;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class TADbAdapter {
	public static final String KEY_DATE = "date";
	public static final String KEY_PERCENT = "percents";
	public static final String KEY_NICO = "nico";
	public static final String KEY_SYNC = "sync";
	public static final String KEY_ROWID = "_id";
	private static final String TAG = "TADbAdapter";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private static final String DATABASE_CREATE1 =
		"create table ta (_id integer primary key autoincrement, "
		+ "date long not null, percents integer not null, " 
		+ "sync integer not null);";
	private static final String DATABASE_CREATE2 =
		"create table nico (_id integer primary key autoincrement, "
		+ "date long not null, nico double not null, "
		+ "sync integer not null);";
	private static final String DATABASE_NAME = "data_tab";
	private static final String DATABASE_TABLE1 = "ta";
	private static final String DATABASE_TABLE2 = "nico";
	private static final int DATABASE_VERSION = 5;
	private final Context mCtx;
	
	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE1);
			db.execSQL(DATABASE_CREATE2);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			//db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE1);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE2);
			db.execSQL(DATABASE_CREATE2);
		}
	}

	public TADbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	public TADbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public long createNewRecord(int percents, long millis) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_PERCENT, percents);
		initialValues.put(KEY_DATE, millis);
		initialValues.put(KEY_SYNC, 0);

		return mDb.insert(DATABASE_TABLE1, null, initialValues);
	}

	public long createNewRecord(int percents) {
		return createNewRecord(percents, System.currentTimeMillis());
	}

	public boolean deleteRecord(long rowId) {
		return mDb.delete(DATABASE_TABLE1, KEY_ROWID + "=" + rowId, null) > 0;
	}

	public boolean deleteSyncedRecords() {
		// TODO: nimp
		long lastWeek = System.currentTimeMillis();
		lastWeek -= 604800000;
		return mDb.delete(DATABASE_TABLE1, 
				KEY_SYNC + "=" + 1 + " AND " + KEY_DATE + "<" + lastWeek,
				null) > 0;
	}

	public Cursor fetchAllRecord() {
		return mDb.query(DATABASE_TABLE1, new String[] {KEY_ROWID, KEY_DATE,
				KEY_SYNC, KEY_PERCENT}, null, null, null, null, null);
	}

	public long getOlderDateRecord() throws SQLException {
		long d = 0;
		Cursor c =
			mDb.query(true, DATABASE_TABLE1, new String[] {KEY_ROWID,
					KEY_DATE,KEY_SYNC, KEY_PERCENT}, null, null,
					null, null, KEY_DATE+" ASC", "1");
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			d = c.getLong(c.getColumnIndex(KEY_DATE));
		}
		c.close();
		return d;
	}

	public long getNewerDateRecord() throws SQLException {
		long d = 0;
		Cursor c =
			mDb.query(true, DATABASE_TABLE1, new String[] {KEY_ROWID,
					KEY_DATE,KEY_SYNC, KEY_PERCENT}, null, null,
					null, null, KEY_DATE+" DESC", "1");
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			d = c.getLong(c.getColumnIndex(KEY_DATE));
		}
		c.close();
		return d;
	}

	public TARecord getRecord(long rowId) throws SQLException {
		TARecord r = null;
		Cursor c =
			mDb.query(true, DATABASE_TABLE1, new String[] {KEY_ROWID,
					KEY_DATE,KEY_SYNC, KEY_PERCENT}, KEY_ROWID + "=" + rowId, null,
					null, null, null, null);
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			r = new TARecord();
			r.date = c.getLong(c.getColumnIndex(KEY_DATE));
			r.id = c.getLong(c.getColumnIndex(KEY_ROWID));
			r.percent = c.getInt(c.getColumnIndex(KEY_PERCENT));
			r.sync = c.getInt(c.getColumnIndex(KEY_SYNC));
		}
		c.close();
		return r;
	}

	public TARecord[] getRecords(long milli) throws SQLException {
		TARecord[] r = null;
		Cursor c =
			mDb.query(true, DATABASE_TABLE1, new String[] {KEY_ROWID,
					KEY_DATE,KEY_SYNC, KEY_PERCENT}, KEY_DATE + ">" + milli, null,
					null, null, KEY_DATE + " ASC", null);
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			r = new TARecord[c.getCount()];
			int id = c.getColumnIndex(KEY_DATE);
			int ii = c.getColumnIndex(KEY_ROWID);
			int ip = c.getColumnIndex(KEY_PERCENT);
			int is = c.getColumnIndex(KEY_SYNC);
			for (int i = 0; i<c.getCount(); i++) {
				r[i] = new TARecord();
				r[i].date = c.getLong(id);
				r[i].id = c.getLong(ii);
				r[i].percent = c.getInt(ip);
				r[i].sync = c.getInt(is);
				c.moveToNext();
			}
		}
		c.close();
		return r;
	}

	public int getPercents(long milli) {
		int p = 0;
		Cursor c =
			mDb.query(true, DATABASE_TABLE1, new String[] {"SUM("+KEY_PERCENT+")"}, KEY_DATE + ">" + milli, null,
					null, null, null, "1");
		if (c != null && c.getCount() > 0) {
			p = c.getInt(0);
		}
		c.close();
		return p;
	}

	public void setLastRecordPercent(int p) {
		long d = 0;
		Cursor c =
			mDb.query(true, DATABASE_TABLE1, new String[] {KEY_ROWID,
					KEY_DATE,KEY_SYNC, KEY_PERCENT}, null, null,
					null, null, KEY_DATE+" DESC", "1");
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			d = c.getInt(c.getColumnIndex(KEY_ROWID));
		}
		ContentValues args = new ContentValues();
		args.put(KEY_PERCENT, p);
		mDb.update(DATABASE_TABLE1, args, KEY_ROWID + "=" + d, null);
		c.close();
	}

	public boolean updateRecord(long rowId, int percents, long millis) {
		ContentValues args = new ContentValues();
		args.put(KEY_PERCENT, percents);
		args.put(KEY_DATE, millis);
		args.put(KEY_SYNC, 0);

		return mDb.update(DATABASE_TABLE1, args, KEY_ROWID + "=" + rowId, null) > 0;
	}

	public TANico getLastestNico() {
		TANico r = null;
		Cursor c =
			mDb.query(true, DATABASE_TABLE2, new String[] {KEY_ROWID,
					KEY_DATE,KEY_SYNC, KEY_NICO}, null, null,
					null, null, KEY_DATE + " DESC", "1");
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			r = new TANico();
			r.date = c.getLong(c.getColumnIndex(KEY_DATE));
			r.id = c.getLong(c.getColumnIndex(KEY_ROWID));
			r.nico = c.getDouble(c.getColumnIndex(KEY_PERCENT));
			r.sync = c.getInt(c.getColumnIndex(KEY_SYNC));
		}
		c.close();
		return r;
	}

	public TANico[] getNicosSince(long milli) throws SQLException {
		TANico[] r = null;
		Cursor c =
			mDb.query(true, DATABASE_TABLE2, new String[] {KEY_ROWID,
					KEY_DATE,KEY_SYNC, KEY_NICO}, KEY_DATE + ">" + milli, null,
					null, null, KEY_DATE + " DESC", null);
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			r = new TANico[c.getCount()];
			int id = c.getColumnIndex(KEY_DATE);
			int ii = c.getColumnIndex(KEY_ROWID);
			int ip = c.getColumnIndex(KEY_NICO);
			int is = c.getColumnIndex(KEY_SYNC);
			for (int i = 0; i<c.getCount(); i++) {
				r[i] = new TANico();
				r[i].date = c.getLong(id);
				r[i].id = c.getLong(ii);
				r[i].nico = c.getDouble(ip);
				r[i].sync = c.getInt(is);
				c.moveToNext();
			}
		}
		c.close();
		return r;
	}

	public TANico[] getNicos(int count) throws SQLException {
		TANico[] r = null;
		Cursor c =
			mDb.query(true, DATABASE_TABLE2, new String[] {KEY_ROWID,
					KEY_DATE,KEY_SYNC, KEY_NICO}, null, null,
					null, null, KEY_DATE + " DESC", Integer.toString(count));
		if (c != null && c.getCount() > 0) {
			c.moveToFirst();
			r = new TANico[c.getCount()];
			int id = c.getColumnIndex(KEY_DATE);
			int ii = c.getColumnIndex(KEY_ROWID);
			int ip = c.getColumnIndex(KEY_NICO);
			int is = c.getColumnIndex(KEY_SYNC);
			for (int i = 0; i<c.getCount(); i++) {
				r[i] = new TANico();
				r[i].date = c.getLong(id);
				r[i].id = c.getLong(ii);
				r[i].nico = c.getDouble(ip);
				r[i].sync = c.getInt(is);
				c.moveToNext();
			}
		}
		c.close();
		return r;
	}

	public long setNico(double nico) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_NICO, nico);
		initialValues.put(KEY_DATE, System.currentTimeMillis());
		initialValues.put(KEY_SYNC, 0);

		return mDb.insert(DATABASE_TABLE2, null, initialValues);
	}
}
